/****** Object:  Stored Procedure dbo.CGO_TriviaInitialize    Script Date: Monday, March 07, 2011 ******/
if exists (select * from dbo.sysobjects where id = object_id(N'dbo.CGO_TriviaInitialize') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure dbo.CGO_TriviaInitialize
GO

/* -------------------------------------------------------------------------------------
/   AccountGet
/  ------------------------------------------------------------------------------------- */
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.CGO_TriviaInitialize	
  @EnrollID INT,
  @UserID INT,
  @CNT_Category INT,
  @CNT_Question INT
AS
  IF NOT EXISTS ( SELECT  1
                  FROM    dbo.CGO_GameEnrollment M
                  INNER JOIN dbo.CGO_GameEnrollmentStatus S ON M.EnrollStatusID=S.StatusID
                  WHERE   EnrollmentID = @EnrollID
                          AND UserID = @UserID 
                          AND StatusCode='PAID') 
  BEGIN
  	RETURN
  END                          
    
  DECLARE @QuestionCount INT  
  DECLARE @CategoryID INT
  CREATE TABLE #QuestionPool
  (
    CategoryID INT,
    QuestionID INT,
    QuestionGUID UNIQUEIDENTIFIER
  )
  DELETE  FROM dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
  
  SET ROWCOUNT @CNT_Category
  
  SELECT  CategoryID
  INTO    #CategoryPool
  FROM    dbo.CGO_TriviaCategory
  ORDER BY NEWID()   
  
  --IF EXISTS (SELECT 1 FROM #CategoryPool WHERE CategoryID=1)
  --BEGIN
  --	PRINT 'Test for insufficient questions in a category'
  --END
  
  WHILE EXISTS (SELECT 1 FROM #CategoryPool)
  BEGIN
  	SELECT TOP 1 @CategoryID = CategoryID FROM #CategoryPool  	
  	
  	SET ROWCOUNT @CNT_Question
  	
    INSERT  INTO #QuestionPool
    ( CategoryID ,
      QuestionID ,
      QuestionGUID 
    )
    SELECT  Q.CategoryID ,
            Q.QuestionID ,
            NEWID()
    FROM    dbo.CGO_TriviaQuestion Q
    WHERE   Q.CategoryID = @CategoryID
            AND NOT EXISTS ( SELECT 1
                             FROM   dbo.CGO_TriviaResult M
                                    INNER JOIN dbo.CGO_GameEnrollment G ON M.GameEnrollmentID = G.EnrollmentID
                                    INNER JOIN dbo.CGO_GameEnrollmentStatus S ON G.EnrollStatusID = S.StatusID
                             WHERE  G.UserID = @UserID                                    
                                    AND s.StatusCode IN ( 'PLAYING', 'COMPLETED' )
                                    AND M.QuestionID = Q.QuestionID )            
    ORDER BY NEWID()
  	
  	DELETE FROM #CategoryPool WHERE CategoryID = @CategoryID
  END
  
  SELECT @QuestionCount = COUNT(*) FROM #QuestionPool
  IF @QuestionCount < @CNT_Category * @CNT_Question
  BEGIN
    SET @CNT_Question = @CNT_Category * @CNT_Question - @QuestionCount
  	SET ROWCOUNT @CNT_Question
  	
  	INSERT  INTO #QuestionPool
    ( CategoryID ,
      QuestionID ,
      QuestionGUID 
    )
    SELECT  Q.CategoryID ,
            Q.QuestionID ,
            NEWID()
    FROM    dbo.CGO_TriviaQuestion Q
    WHERE   NOT EXISTS ( SELECT 1
                         FROM   dbo.CGO_TriviaResult M
                                INNER JOIN dbo.CGO_GameEnrollment G ON M.GameEnrollmentID = G.EnrollmentID
                                INNER JOIN dbo.CGO_GameEnrollmentStatus S ON G.EnrollStatusID = S.StatusID
                         WHERE  G.UserID = @UserID                                
                                AND S.StatusCode IN ( 'PLAYING', 'COMPLETED' )
                                AND M.QuestionID = Q.QuestionID )
            AND NOT EXISTS ( SELECT 1
                             FROM   #QuestionPool P
                             WHERE  P.QuestionID = Q.QuestionID )
    ORDER BY NEWID()  	
  END
  
  SET ROWCOUNT 0
  
  INSERT  INTO dbo.CGO_TriviaResult
  ( GameEnrollmentID ,
    QuestionIndex ,
    QuestionID,
    ResultStatusID
  )
  SELECT  @EnrollID ,
          ROW_NUMBER() OVER ( ORDER BY NEWID(), QuestionGUID ) ,
          QuestionID,
          1
  FROM    #QuestionPool
  
  DROP TABLE #CategoryPool
  DROP TABLE #QuestionPool
  
  SELECT  COUNT(*)
  FROM    dbo.CGO_TriviaResult
  WHERE   GameEnrollmentID = @EnrollID
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO